﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Windows.Forms;

namespace SfcorpDLL.biz
{
    public static class bizTableCross
    {
        public static DataTable DataTableCross(DataTable dtSour, string tableName, string crossColumn, string sumColumn)
        {
            string addCol = string.Empty;
            string sFilter = string.Empty;
            bool isFirstView = true;
            DataTable dtCro = dtSour.Clone();

            #region 取得关键字已经存在字段名
            string[] sCroColNames = new string[dtCro.Columns.Count];
            for (int i = 0; i < dtCro.Columns.Count; i++)
            {
                sCroColNames[i] = dtCro.Columns[i].ColumnName;
            }
            #endregion
            //写入交叉字段到新表
            foreach (DataRow drSour in dtSour.Select("", crossColumn))
            {
                addCol = drSour[crossColumn].ToString().Substring(1);
                if (addCol == crossColumn || addCol == sumColumn)
                    continue;
                if (dtCro.Columns.Contains(addCol) == false)
                {
                    dtCro.Columns.Add(addCol);
                }
            }

            //汇总统计数据
            Hashtable htFixValues = new Hashtable();
            string sFixVals = string.Empty;
            DataRow drCro = dtCro.NewRow();
            DataRow[] drCroRows;
            double dbCro;
            double dbSour;
            //int i
            foreach (DataRow drAmount in dtSour.Rows)
            {
                sFixVals = string.Empty;
                foreach (string sCols in sCroColNames)
                {
                    sFixVals += drAmount[sCols].ToString();
                }
                if (htFixValues.Contains(sFixVals) == false)
                {
                    #region 新加数据
                    htFixValues.Add(sFixVals, "");
                    //不包括则增加数据到交叉表中
                    drCro = dtCro.NewRow();
                    foreach (string sCols in sCroColNames)
                    {
                        drCro[sCols] = drAmount[sCols];
                    }
                    //交叉字段赋值
                    drCro[drAmount[crossColumn].ToString().Substring(1)] = drAmount[sumColumn];
                    dtCro.Rows.Add(drCro);
                    #endregion
                }
                else   //更新数据
                {

                    #region 更新数据
                    sFilter = string.Empty;
                    string sCroName = drAmount[crossColumn].ToString().Substring(1);
                    foreach (string sCols in sCroColNames)
                    {
                        sFilter += sCols + "='" + drAmount[sCols].ToString() + "' and ";
                    }
                    sFilter = sFilter.Substring(0, sFilter.Length - 4);

                    //20140410交叉报表必须所有交叉的数据必须用nvl(FieldName,' ')这样，否则下面过滤器找不到记录报错
                    //如下：nvl(c.GOODSNAME,' ') GOODSNAME,nvl(CARRIER_NOTE_NO,' ') CARRIER_NOTE_NO,nvl(CONTRACT_NO,' ')CONTRACT_NO
                    if (sFilter.IndexOf("''") > 0 && isFirstView)
                    {
                        isFirstView = false;
                        MessageBox.Show("交叉报表必须所有交叉的数据必须用nvl(FieldName,' ')" + sFilter);
                    }
                    drCroRows = dtCro.Select(sFilter);
                    if (drCroRows != null && drCroRows.Length > 0)
                    {
                        drCro = drCroRows[0];
                        double.TryParse(drCro[sCroName].ToString(), out dbCro);
                        double.TryParse(drAmount[sumColumn].ToString(), out dbSour);
                        drCro[sCroName] = (dbCro + dbSour).ToString().Trim();
                    }
                    #endregion

                }
            }

            dtCro.TableName = tableName + "_CROSS";
            return dtCro;
            //dsSource.Tables.Add(dtCro);
        }
    }
}
